#!/usr/bin/python
# -*- coding:UTF-8 -*-
from plan_util import *


class Achievement_decoration(Util):
    def __init__(self, host='', username='', password='', media='', pthost='', charset='utf8'):
        self.dbconn = None
        self.cursor = None
        self.flag = True

        self.host = host
        self.username = username
        self.password = password
        self.charset = charset
        self.media = media
        self.pthost = pthost

        self.column = u"""
        d.orderId AS `装修单号`,
        v.name AS `楼盘`,
        h.houseNumber AS `楼号`,
        regionds.no AS `设计大区ID`,
        regionds.name AS `设计分区`,
        deptds.no AS `设计部ID`,
        deptds.name AS `设计部`,
        ds.userId AS `设计师ID`,
        ds.currentName AS `设计师`,
        regionsm.no AS `业务分区ID`,
        regionsm.name AS `业务分区`,
        deptsm.no AS `业务部ID`,
        deptsm.name AS `业务部`,
        sm.userId AS `业务员ID`,
        sm.currentName AS `业务员`,
        d.orderTotalPrice AS `签单额`,
        d.couponCut AS `券优惠额`,
        d.thingCut AS `奖品优惠额`,
        d.netperfor AS `净业绩`,
        d.comperfor AS `复合业绩`,
        d.contractTime AS `签单时间`,
        ds.uid AS `设计师uid`,
        sm.uid AS `业务员uid`,
        d.type AS `装修单类型`,
        cc.name AS `公司`,
        belongds.rate AS `设计师比例`,
        belongsm.rate AS `业务员比例`,
        d.discount AS `订单折扣0为10折`,
        d.notSalesmanRate  AS `非业务人员提点`,
        qq.disPrice  AS `打折总额`,
        qq.undisPrice  AS `不打折项总额`,
        qq.managePrice AS`管理费`,
        qq.trafficPrice  AS `车运费`,
        qq.manageRate AS `管理费比例`
        """
        self.SQL_EXPORT_ACHIEVEMENT_DECORATION_COLLECTER = u"""
         	 SELECT 
         	  %s
            FROM
            decoration.decoration AS d,
            communion.house AS h,
            communion.village AS v,
            communion.user AS ds,
            communion.user AS sm,
            communion.department AS deptds,
            communion.department AS regionds,
            communion.department AS deptsm,
            communion.department AS regionsm,
            decoration.decorationbelong AS belongds,
            decoration.decorationbelong AS belongsm,
            communion.company AS cc,
            quotation.quotation AS qq
            WHERE
            d.hid = h.hid 
            AND h.vid = v.vid
            AND	d.orderId = belongds.orderId
            AND	d.orderId = belongsm.orderId
            AND belongds.buid = ds.uid 
            AND belongds.did = deptds.dId
            AND belongds.rid = regionds.dId 
            AND belongds.`status` = 0
            AND belongds.`type` = 0
            AND belongsm.buid = sm.uid 
            AND belongsm.did = deptsm.dId
            AND belongsm.rid = regionsm.dId 
            AND belongsm.`status` = 0
            AND belongsm.`type` = 1
            AND	d.company = cc.cid
            AND qq.orderId = d.orderId
            AND qq.defaults = 1
            %s
            AND d.contractTime >= '%s'
            AND d.contractTime <= '%s' 
            """
        self.user = {}  # 设计师2
        self.dsUser = {}  # 业务员3
        self.designArea = {}  # 设计大区4
        self.designUnit = {}  # 设计部5
        self.designRegion = {}  # 业务大区6
        self.businessUnit = {}  # 业务部7

    def assortDate(self, v):

        type = v[0]  # 装修单类型
        smuid = v[1]  # 设计师uid
        netperfor = v[2]  # 净业绩
        comperfor = v[3]  # 复合业绩
        smUserId = v[4]  # 设计师ID
        smUserName = v[5]  # 设计师名称
        designRegionID = v[6]  # 设计大区ID
        designRegionName = v[7]  # 设计分区
        designDept = v[8]  # 设计部门ID
        designDeptName = v[9]  # 设计部门

        dsuid = v[10]  # 业务员uid
        dsUserId = v[11]  # 业务员ID
        dsUserName = v[12]  # 业务员名称
        businessPartitionID = v[13]  # 业务分区ID
        businessPartitionName = v[14]  # 业务分区
        businessPartitionDept = v[15]  # 业务部门ID
        businessPartitionDeptName = v[16]  # 业务部门
        # 17

        if type != 0:
            prics = netperfor  # 整装/半价 净业绩
        else:
            prics = comperfor  # 半包     复合业绩

        users = (smUserId, smUserName, prics)  # 设计师
        dsusers = (dsUserId, dsUserName, prics)  # 业务员
        designAreas = (designRegionID, designRegionName, prics)  # 设计大区
        designUnits = (designDept, designDeptName, prics)  # 设计部
        designRegions = (businessPartitionID, businessPartitionName, prics)  # 业务大区
        businessUnits = (businessPartitionDept, businessPartitionDeptName, prics)  # 业务部

        # 设计师
        if not self.user.has_key(smuid):
            self.user[smuid] = users
        else:
            users = list(self.user[smuid])
            sumPrice = users[2] + prics
            self.user[smuid] = (smUserId, smUserName, sumPrice)

        # 业务员
        if not self.dsUser.has_key(dsuid):
            self.dsUser[smuid] = dsusers
        else:
            dsusers = list(self.dsUser[dsuid])
            sumPrice = dsusers[2] + prics
            self.dsUser[dsuid] = (dsUserId, dsUserName, sumPrice)

        # 设计大区
        if not self.designArea.has_key(designRegionID):
            self.designArea[designRegionID] = designAreas
        else:
            designAreas = list(self.designArea[designRegionID])
            sumPrice = designAreas[2] + prics
            self.designArea[designRegionID] = (designRegionID, designRegionName, sumPrice)

        # 设计部
        if not self.designUnit.has_key(designDept):
            self.designUnit[designDept] = designUnits
        else:
            designUnits = list(self.designUnit[designDept])
            sumPrice = designUnits[2] + prics
            self.designUnit[designDept] = (designDept, designDeptName, sumPrice)

        # 业务大区
        if not self.designRegion.has_key(businessPartitionID):
            self.designRegion[businessPartitionID] = designRegions
        else:
            designRegions = list(self.designRegion[businessPartitionID])
            sumPrice = designRegions[2] + prics
            self.designRegion[businessPartitionID] = (businessPartitionID, businessPartitionName, sumPrice)

        # 业务部
        if not self.businessUnit.has_key(businessPartitionDept):
            self.businessUnit[businessPartitionDept] = businessUnits
        else:
            businessUnits = list(self.businessUnit[businessPartitionDept])
            sumPrice = businessUnits[2] + prics
            self.businessUnit[businessPartitionDept] = (businessPartitionDept, businessPartitionDeptName, sumPrice)

    def sheet2(self, workbook):
        self.user = self.user.values()
        self.user = sorted(self.user, key=lambda user: user[2], reverse=True)
        tableHeadTwo = [u"人员ID", u"设计师姓名", u"总额"]
        sheet2 = workbook.add_sheet(u"签单额排名-设计师", cell_overwrite_ok=True)
        self.createTableHead(sheet2, tableHeadTwo)

        portrait = 1
        for i, v in enumerate(self.user):
            for j, k in enumerate(v):
                self.writeWorkbook(sheet2, portrait, j, k)
            portrait = portrait + 1

    def sheet3(self, workbook):
        self.dsUser = self.dsUser.values()
        self.dsUser = sorted(self.dsUser, key=lambda user: user[2], reverse=True)
        tableHead3 = [u"人员ID", u"业务员姓名", u"总额"]
        sheet3 = workbook.add_sheet(u"签单额排名-业务员", cell_overwrite_ok=True)
        self.createTableHead(sheet3, tableHead3)

        portraits = 1
        for i, v in enumerate(self.dsUser):
            for j, k in enumerate(v):
                self.writeWorkbook(sheet3, portraits, j, k)
            portraits = portraits + 1

    def sheet4(self, workbook):
        self.designArea = self.designArea.values()
        self.designArea = sorted(self.designArea, key=lambda designArea: designArea[2], reverse=True)
        tableHead4 = [u"业务大区ID", u"业务大区", u"总额"]
        sheet4 = workbook.add_sheet(u"签单额排名-设计大区", cell_overwrite_ok=True)
        self.createTableHead(sheet4, tableHead4)

        portraits = 1
        for i, v in enumerate(self.designArea):
            for j, k in enumerate(v):
                self.writeWorkbook(sheet4, portraits, j, k)
            portraits = portraits + 1

    def sheet5(self, workbook):
        self.designUnit = self.designUnit.values()
        self.designUnit = sorted(self.designUnit, key=lambda designUnit: designUnit[2], reverse=True)
        tableHead5 = [u"设计部ID", u"设计部", u"总额"]
        sheet5 = workbook.add_sheet(u"签单额排名-设计部", cell_overwrite_ok=True)
        self.createTableHead(sheet5, tableHead5)

        portraits = 1
        for i, v in enumerate(self.designUnit):
            for j, k in enumerate(v):
                self.writeWorkbook(sheet5, portraits, j, k)
            portraits = portraits + 1

    def sheet6(self, workbook):
        self.designRegion = self.designRegion.values()
        self.designRegion = sorted(self.designRegion, key=lambda designRegion: designRegion[2], reverse=True)
        tableHead6 = [u"业务大区ID", u"业务大区", u"总额"]
        sheet6 = workbook.add_sheet(u"签单额排名-业务大区", cell_overwrite_ok=True)
        self.createTableHead(sheet6, tableHead6)

        portraits = 1
        for i, v in enumerate(self.designRegion):
            for j, k in enumerate(v):
                self.writeWorkbook(sheet6, portraits, j, k)
            portraits = portraits + 1

    def sheet7(self, workbook):
        self.businessUnit = self.businessUnit.values()
        self.businessUnit = sorted(self.businessUnit, key=lambda businessUnit: businessUnit[2], reverse=True)
        tableHead7 = [u"业务部ID", u"业务部", u"总额"]
        sheet7 = workbook.add_sheet(u"签单额排名-业务部", cell_overwrite_ok=True)
        self.createTableHead(sheet7, tableHead7)

        portraits = 1
        for i, v in enumerate(self.businessUnit):
            for j, k in enumerate(v):
                self.writeWorkbook(sheet7, portraits, j, k)
            portraits = portraits + 1

    def main(self, pid, startTime, endTime, companys):
        try:
            falgs = self.controlTime(startTime, endTime)
            if falgs == False:
                raise RuntimeError(u'时间应在1-31内')

            tableHead = [u"装修单号", u"楼盘", u"楼号", u"设计大区ID", u"设计分区", u"设计部ID", u"设计部", u" 设计师ID", u"设计师 ", u" 业务分区ID",
                         u"业务分区", u"业务部ID", u"业务部", u" 业务员ID", u"业务员", u"签单额", u"券优惠额 ", u"奖品优惠额", u"净业绩 ", u" 复合业绩",
                         u"签单时间", u"公司", u"比例", u"折扣", u"类型", u"非业务人员提点", u"非业务人员提点金额"]
            tableHeadLen = len(tableHead)

            self.getDBCursor(self.host, self.username, self.password, self.charset)

            term = u" "
            if int(companys) != 0:
                term = u"""AND	d.company = %s""" % companys

            countSQL = self.SQL_EXPORT_ACHIEVEMENT_DECORATION_COLLECTER % ("count(*)", term, startTime, endTime)

            count = self.count(countSQL)

            if count == 0:
                raise RuntimeError(u'The query result is zero!')

            all = count / 0.8

            params = self.createWorkbook(u"sheet1")

            workbook = params[0]
            sheet = params[1]

            self.createTableHead(sheet, tableHead)

            path = tempfile.gettempdir() + u"/decoration.xls"
            self.saveWorkbook(path, workbook)

            molecular = all * 0.1
            schedule = (molecular / all) * 100

            paln = {
                u"plan.schedule": int(schedule),
                u"plan.status": 4
            }
            self.updataSchedule(self.pthost, pid, paln)
            sql = self.SQL_EXPORT_ACHIEVEMENT_DECORATION_COLLECTER % (self.column, term, startTime, endTime)
            nowPage = 1
            length = 1
            flag = True

            while (flag):
                data = self.page(sql, nowPage, size=200)

                for i, v in enumerate(data):
                    try:
                        orderId = v[0]  # 订单号
                        propertiesName = v[1]  # 楼盘名字
                        houseNumber = v[2]  # 楼号
                        type = v[23]  # 装修单类型
                        smuid = v[21]  # 设计师uid
                        orderTotalPrice = float(v[15])  # 签单额
                        couponCut = float(v[16])  # 券优惠额
                        thingCut = float(v[17])  # 奖品优惠额
                        netperfor = float(v[18])  # 净业绩
                        comperfor = float(v[19])  # 复合业绩
                        contractTime = v[20]  # 签单时间
                        types = int(v[23])  # 类型
                        companys = v[24]  # 公司

                        dsRate = float(v[25])  # 设计师合尺比例
                        smRate = float(v[26])  # 业务员合尺比例
                        discount = float(v[27])  # 订单折扣0为10折
                        notSalesmanRate = float(v[28])  # 非业务人员提点

                        sumdisPrice = float(v[29])  # 打折总额
                        undisPrice = float(v[30])  # 不打折项总额
                        managePrice = float(v[31])  # 管理费
                        trafficPrice = float(v[32])  # 车运费
                        manageRate = float(v[33])  # 管理费比例

                        smUserId = v[7]  # 设计师ID
                        smUserName = v[8]  # 设计师名称
                        designRegionID = v[3]  # 设计大区ID
                        designRegionName = v[4]  # 设计分区
                        designDept = v[5]  # 设计部门ID
                        designDeptName = v[6]  # 设计部门

                        dsuid = v[22]  # 业务员uid
                        dsUserId = v[13]  # 业务员ID
                        dsUserName = v[14]  # 业务员名称
                        businessPartitionID = v[9]  # 业务分区ID
                        businessPartitionName = v[10]  # 业务分区
                        businessPartitionDept = v[11]  # 业务部门ID
                        businessPartitionDeptName = v[12]  # 业务部门
                        rates = dsRate * smRate  # 比例

                        biliOrderTotalPrice = orderTotalPrice * rates
                        biliCouponCut = couponCut * rates
                        biliThingCut = thingCut * rates
                        contractTime = u"%s" % contractTime

                        # 推回8.8折合同报价=打折总额*0.88+不打折总额+车运费+管理费
                        back88 = (sumdisPrice * 0.88 + undisPrice + trafficPrice + managePrice) * rates

                        # 非业务人员提点金额=管理费/（管理费比例+非业务提点比例）*非业务提点比例
                        if manageRate + notSalesmanRate == 0:
                            raise RuntimeError(u'管理费比例+非业务提点比例 出现0,该订单号为%s' % orderId)
                        feiTi = (managePrice / (manageRate + notSalesmanRate) * notSalesmanRate) * rates

                        # 复核业绩=[(推回8.8折合同报价—非业务人员提点金额)*0.38-券-礼品+签单额-推回8.8折合同报价]/0.38
                        fuhePrice = ((back88 - feiTi) * 0.38 - biliCouponCut - biliThingCut + biliOrderTotalPrice - back88) / 0.38

                        # 净业绩 = 签单额-非业务人员提点金额-优惠券-赠送礼品
                        jingYeJi = biliOrderTotalPrice - feiTi - biliCouponCut - biliThingCut

                        if discount == 0.00:
                            discount = 10.00

                        typeName = u""
                        if types == 0:
                            typeName = u"半包"
                        elif types == 1:
                            typeName = u"套餐"
                        else:
                            typeName = u"半价"


                        if companys == u"辽宁方林装饰":
                            if types == 0:              #半包
                                if designRegionID != u"FL_DR_05":
                                    pass
                                else:
                                    fuhePrice = jingYeJi
                            else:
                                fuhePrice = jingYeJi
                        else:
                            fuhePrice = jingYeJi

                        fuhePrice = round(fuhePrice,3)
                        jingYeJi = round(jingYeJi,3)
                        biliOrderTotalPrice = round(biliOrderTotalPrice,3)

                        datas = (orderId, propertiesName, houseNumber, designRegionID, designRegionName, designDept,
                                 designDeptName, smUserId, smUserName, businessPartitionID, businessPartitionName,
                                 businessPartitionDept, businessPartitionDeptName, dsUserId, dsUserName,
                                 biliOrderTotalPrice, biliCouponCut, biliThingCut,jingYeJi,fuhePrice , contractTime,
                                 companys, rates,discount, typeName, notSalesmanRate, feiTi)

                        lists = (type, smuid, jingYeJi, fuhePrice, smUserId, smUserName, designRegionID, designRegionName,
                        designDept, designDeptName, dsuid, dsUserId, dsUserName, businessPartitionID,businessPartitionName,
                        businessPartitionDept, businessPartitionDeptName)

                        self.assortDate(lists)

                        for j, k in enumerate(datas):
                            self.writeWorkbook(sheet, length, j, k)
                        length = length + 1

                    except Exception as e:
                        e = u"发现异常!。%s。" % (e)
                        self.errCall(pid, self.pthost, self.media, e, False)

                self.saveWorkbook(path, workbook)

                molecular = molecular + len(data)
                schedules = (molecular / all) * 100

                paln = {
                    u"plan.schedule": int(schedules),
                }

                self.updataSchedule(self.pthost, pid, paln)

                nowPage = nowPage + 1

                if len(data) < 200:
                    flag = False

            self.sheet2(workbook)
            self.sheet3(workbook)
            self.sheet4(workbook)
            self.sheet5(workbook)
            self.sheet6(workbook)
            self.sheet7(workbook)
            self.saveWorkbook(path, workbook)
            url = self.media + u"uploadOther.do"
            resultfilepath = self.uploudFile(path, url)
            self.delFile(path)

            paln = {
                u"plan.resultFilepath": resultfilepath
            }

            self.updataSchedule(self.pthost, pid, paln)

        except Exception as e:
            self.flag = False
            self.errCall(pid, self.pthost, self.media, e)

        finally:

            self.modelEnd(pid, self.pthost, self.media, self.flag)
            sys.exit(0)


if __name__ == '__main__':
    """
        装修单业绩统计,根据签单时间进行查询说明:
        其中: 部门是从decorationbelong表中关联的
        根据查询条件,以分页(200条/次)拿数据的方式导出装修单业绩统计;并上传到文件服务器中,可从任务页面上点击下载即可;
        当该脚本执行出现异常时,将跟新进度为失败并记录到log文件中上传至文件服务器中;
        注: 排名时, 整装/半价 使用净业绩 ,半包使用复合业绩
        导出结果 例子:
        装修单号	    楼盘	        楼号	设计大区ID	设计分区	设计部ID	设计部	        设计师ID	设计师 	 业务分区ID	 业务分区	    业务部ID	业务部	        业务员ID	业务员	签单额	券优惠额 	奖品优惠额	净业绩 	 复合业绩	签单时间
        1603230009	巴黎春天	#12-12-12	FL_DR_04	设计四分区	FL_DD_15	设计中心三部	FL-00168	杜若平	FL_BR_01	市场营销一分区	FL_BD_01	市场营销一部	FL-00638	李宁	50000	0	            0	    0	        0	2016-05-29 06:26:49
        
        sheet2页: 按设计师进行合并,并累加签单额后进行排序(高>低)
          人员ID	    设计师姓名	  签单额	
          FL-06875	李诗瑶	    11273959	
          
        sheet3页: 按业务员进行合并,并累加签单额后进行排序(高>低)
          人员ID	业务员姓名   	签单额	   
          FL-03526	张企	    9999999	 
          
        sheet4页:按设计大区进行合并,并累加签单额后进行排序(高>低)
            设计大区ID	设计大区	        签单额
            FL_DR_10	    大东独立运营分区	22781012.7
            FL_ER_01	    工程大区	        10459614

        sheet5页:按设计部进行合并,并累加签单额后进行排序(高>低)
            设计部ID	设计部	            签单额
            FL_DR_10	大东独立运营分区	22781012.7
            FL_ER_01	工程大区	1       0459614

        sheet6页:按业务大区进行合并,并累加签单额后进行排序(高>低)
            业务大区ID	业务大区	        签单额
            FL_DR_10	    大东独立运营分区	22781012.7
            FL_ER_01	    工程大区	        10459614

        sheet7页:按业务部进行合并,并累加签单额后进行排序(高>低)
            业务部ID	业务部	            签单额
            FL_DR_10	大东独立运营分区	22781012.7
            FL_ER_01	工程大区	        10459614

    1.传入必须参数说明
        1.pid         information.plan的主键;
        2.startTime  开始时间 例"2014-06-14"
        3.endTime    结束时间 例"2018-06-14"
        
    2.使用说用名
        python plan_achievement_decoration_collecter.py pid args company
        python plan_achievement_decoration_collecter.py 288 2014/06/14-2018/06/14 1
    """
    modernFilePath = os.path.realpath(__file__)
    configPath = "%s/%s" % (os.path.dirname(modernFilePath), "config.json")

    config = json.load(open(configPath))
    dbconf = config.get("database")

    # pid = sys.argv[1]
    # args = sys.argv[2]
    # company = sys.argv[3]
    #

    pid = sys.argv[1]
    args = sys.argv[2]
    company = sys.argv[3]

    times = args.split("-")
    startTime = times[0]
    endTime = times[1]
    achievement_decoration = Achievement_decoration(dbconf.get("host"), dbconf.get("username"), dbconf.get("password"),config.get("media"), config.get("pthost"))
    achievement_decoration.main(pid, startTime, endTime, company)
